/*
 * Copyright 2016 Wealth Inc. All rights reserved.
 */
 package com.wealth.commodity.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.wealth.commodity.entity.Comment;

import iaas.repository.BaseRepository;

/**
 * @author chan
 * @version 1.0
 * @since 1.0
 */
 
@Repository
public interface CommentDao extends BaseRepository<Comment, Long> ,JpaSpecificationExecutor<Comment>{

	/** 商户  最近的商品评论  前offset条 */
	@Query(value="SELECT * FROM commodity_comment a, commodity_goods b WHERE a.status=1 and a.goods_id =b.id and  b.merchant_id=?1 ORDER BY a.id DESC LIMIT 0,?2",nativeQuery=true)
	public List<Comment> getLatestCommentByMerchantId(Long merchantid, Integer offset);
	
	/** 商户 商品评价平均分数  */
	@Query(value="SELECT FLOOR(AVG(score)) FROM commodity_comment a, commodity_goods b WHERE  a.status=1 and a.goods_id =b.id and  b.merchant_id=?1",nativeQuery=true)
	public Integer getAvgScoreByMerchantId(Long merchantid);
	
	/** 商户 商品评价总数 */
	@Query(value="SELECT COUNT(*) FROM commodity_comment a, commodity_goods b WHERE a.status=1 and a.goods_id =b.id and  b.merchant_id=?1",nativeQuery=true)
	public Integer getCommentNumByMerchantId(Long merchantid);
	
	
	/** 商品 最近的评论  前offset条 */
	@Query(value="SELECT * FROM commodity_comment a where a.status=1 and a.goods_id=?1 ORDER BY a.id DESC LIMIT 0,?2",nativeQuery=true)
	public List<Comment> getLatestComment(Long goodsid, Integer offset);
	
	/** 商品 评价平均分数  */
	@Query(value="SELECT FLOOR(AVG(score)) FROM commodity_comment a where a.status=1 and a.goods_id=?1",nativeQuery=true)
	public Integer getAvgScore(Long goodsid);
	
	/** 商品 评价总数 */
	@Query(value="SELECT COUNT(*) FROM commodity_comment a where a.status=1 and a.goods_id=?1",nativeQuery=true)
	public Integer getCommentNum(Long goodsid);
	
	
	/* 是否重复提交评论 */
	public List<Comment> findByMemberIdAndOrderIdAndGoodsId(Long memberid,Long orderid,Long goodsid);
}

